package com.orangelife.service.property.impl;

import java.io.ByteArrayOutputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPrintSetup;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.util.CellRangeAddress;
import org.junit.Test;
import org.springframework.stereotype.Component;

import com.orangelife.common.MybaitsGenericDaoSubLogic;
import com.orangelife.exception.AppBizException;

@Component("exportFeeNotice")
public class ExportFeeNotice extends MybaitsGenericDaoSubLogic {

	public void outExcel(List<Map<String, Object>> list, String limitDate,
			String printtime, String commName, String remark, String startTime,
			String endTime) throws AppBizException, ParseException {

		String sfqj = "";
		if (startTime.equals(endTime)) {
			sfqj = startTime;
		} else {
			sfqj = startTime + "~" + endTime;
		}

		HSSFWorkbook wb = new HSSFWorkbook(); // --->创建了一个excel文件
		HSSFSheet sheet = wb.createSheet("收费通知单"); // --->创建了一个工作簿

		HSSFPrintSetup printSetup = sheet.getPrintSetup();
		printSetup.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE); // 纸张
		// sheet.setDisplayGridlines(false);
		// sheet.setPrintGridlines(false);System.out.println("vvvvvvvvvvvvvv:"+printSetup.getFitHeight());
		sheet.setMargin(HSSFSheet.TopMargin, (double) 0.5); // 上边距
		sheet.setMargin(HSSFSheet.BottomMargin, (double) 0.5); // 下边距
		sheet.setMargin(HSSFSheet.LeftMargin, (double) 0.5); // 左边距
		sheet.setMargin(HSSFSheet.RightMargin, (double) 0.5); // 右边距

		HSSFDataFormat format = wb.createDataFormat(); // --->单元格内容格式
		sheet.setColumnWidth((short) 0, 20 * 190); // ---》设置单元格宽度，因为一个单元格宽度定了那么下面多有的单元格高度都确定了所以这个方法是sheet的
		sheet.setColumnWidth((short) 1, 20 * 170); // --->第一个参数是指哪个单元格，第二个参数是单元格的宽度
		sheet.setColumnWidth((short) 2, 20 * 170);
		sheet.setColumnWidth((short) 3, 20 * 170);
		sheet.setColumnWidth((short) 4, 20 * 330);
		sheet.setColumnWidth((short) 5, 20 * 170);
		sheet.setDefaultRowHeight((short) 300); // ---->有得时候你想设置统一单元格的高度，就用这个方法
		sheet.protectSheet("123456");
		int sheetrow = 0;
		int numrow = 0;
		/** 所有行的样式开始 **/
		// 样式1 第一行和第二行样式
		HSSFCellStyle style = wb.createCellStyle(); // 样式对象
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
		style.setBorderBottom((short) 1); // 设置下划线，参数是黑线的宽度
		style.setBorderLeft((short) 1); // 设置左边框
		style.setBorderRight((short) 1); // 设置有边框
		style.setBorderTop((short) 1); // 设置下边框
		Font font = wb.createFont();
		// font.setFontName("Courier New"); //---》设置字体，是什么类型例如：宋体
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗
		// font.setItalic(true); //--->设置是否是加粗
		font.setFontHeightInPoints((short) 10); // --->设置字体大小
		style.setFont(font);

		// 第三行样式
		HSSFCellStyle style3 = wb.createCellStyle(); // 样式对象
		style3.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直
		style3.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
		// style3.setBorderBottom((short)0); //设置下划线，参数是黑线的宽度
		// style3.setBorderLeft((short)0); //设置左边框
		// style3.setBorderRight((short)1); //设置有边框
		// style3.setBorderTop((short)0); //设置下边框
		Font font3 = wb.createFont();
		font3.setFontHeightInPoints((short) 5); // --->设置字体大小
		style3.setFont(font3);

		HSSFCellStyle stylecel = wb.createCellStyle(); // 样式对象
		stylecel.setBorderBottom((short) 0); // 设置下划线，参数是黑线的宽度
		stylecel.setBorderLeft((short) 1); // 设置左边框
		stylecel.setBorderRight((short) 0); // 设置有边框
		stylecel.setBorderTop((short) 0); // 设置下边框

		HSSFCellStyle stylecel1 = wb.createCellStyle(); // 样式对象
		stylecel1.setBorderBottom((short) 0); // 设置下划线，参数是黑线的宽度
		stylecel1.setBorderLeft((short) 0); // 设置左边框
		stylecel1.setBorderRight((short) 0); // 设置有边框
		stylecel1.setBorderTop((short) 0); // 设置下边框

		HSSFCellStyle stylecel2 = wb.createCellStyle(); // 样式对象
		stylecel2.setBorderBottom((short) 0); // 设置下划线，参数是黑线的宽度
		stylecel2.setBorderLeft((short) 0); // 设置左边框
		stylecel2.setBorderRight((short) 1); // 设置有边框
		stylecel2.setBorderTop((short) 0); // 设置下边框

		// 样式1 第一行和第二行样式
		HSSFCellStyle style5 = wb.createCellStyle(); // 样式对象
		style5.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直
		style5.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
		style5.setBorderBottom((short) 1); // 设置下划线，参数是黑线的宽度
		style5.setBorderLeft((short) 1); // 设置左边框
		style5.setBorderRight((short) 1); // 设置有边框
		style5.setBorderTop((short) 1); // 设置下边框
		Font font5 = wb.createFont();
		font5.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗
		// font.setFontName("Courier New"); //---》设置字体，是什么类型例如：宋体
		// font5.setItalic(true); //--->设置斜体
		font5.setFontHeightInPoints((short) 8); // --->设置字体大小
		// font5.set
		style5.setFont(font5);

		HSSFCellStyle style6 = wb.createCellStyle(); // 样式对象
		style6.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直
		style6.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
		style6.setBorderBottom((short) 1); // 设置下划线，参数是黑线的宽度
		style6.setBorderLeft((short) 1); // 设置左边框
		style6.setBorderRight((short) 1); // 设置有边框
		style6.setBorderTop((short) 1); // 设置下边框
		style6.setWrapText(true); // 设置是否能够换行，能够换行为true

		// 表格第11行
		HSSFCellStyle style11 = wb.createCellStyle(); // 样式对象
		style11.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直
		// style11.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
		style11.setBorderBottom((short) 1); // 设置下划线，参数是黑线的宽度
		style11.setBorderLeft((short) 1); // 设置左边框
		style11.setBorderRight((short) 1); // 设置有边框
		style11.setBorderTop((short) 1); // 设置下边框

		// 表格第12行
		HSSFCellStyle style12 = wb.createCellStyle(); // 样式对象
		style12.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直
		style12.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
		style12.setBorderBottom((short) 1); // 设置下划线，参数是黑线的宽度
		style12.setBorderLeft((short) 1); // 设置左边框
		style12.setBorderRight((short) 1); // 设置有边框
		style12.setBorderTop((short) 1); // 设置下边框
		style12.setWrapText(true); // 设置是否能够换行，能够换行为true
		/** 所有行的样式结束 **/
		// //设置标题字体格式
		// // Font font = wb.createFont();
		// Font font1 = wb.createFont();
		// //设置字体样式
		// // font.setFontHeightInPoints((short)20); //--->设置字体大小
		// // font.setFontName("Courier New"); //---》设置字体，是什么类型例如：宋体
		// font1.setItalic(true); //--->设置是否是加粗
		// style1.setFont(font1); //--->将字体格式加入到style1中
		// //style1.setFillForegroundColor(IndexedColors.DARK_YELLOW.getIndex());
		// //style1.setFillPattern(CellStyle.SOLID_FOREGROUND);设置单元格颜色
		// style1.setWrapText(true); //设置是否能够换行，能够换行为true
		// style1.setBorderBottom((short)1); //设置下划线，参数是黑线的宽度
		// style1.setBorderLeft((short)1); //设置左边框
		// style1.setBorderRight((short)1); //设置有边框
		// style1.setBorderTop((short)1); //设置下边框
		// style4.setDataFormat(format.getFormat("￥#,##0")); //--->设置为单元格内容为货币格式
		//
		// style5.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%"));
		// //--->设置单元格内容为百分数格式

		for (int i = 0; i < list.size(); i++) {
			Map<String, Object> map = list.get(i);
			String fwxx = (String) map.get("building_name")
					+ (String) map.get("unit_name")
					+ (String) map.get("apartment_num");
			String fwjzmj = (String) map.get("fee_area");
			// String
			int tzdgd = 0;// 通知单高度
			// 表格第一行
			numrow = sheetrow++;
			HSSFRow row0 = sheet.createRow(numrow); // --->创建一行

			// 四个参数分别是：起始行，起始列，结束行，结束列
			sheet.addMergedRegion(new CellRangeAddress(numrow, (short) numrow,
					0, (short) 5));
			row0.setHeightInPoints(20);
			row0.setRowStyle(style);
			tzdgd += row0.getHeight();
			HSSFCell cell0 = row0.createCell(0); // --->创建一个单元格
			cell0.setCellStyle(style);
			cell0.setCellValue("厦门新凯宾物业服务有限公司");

			// 表格第二行
			numrow = sheetrow++;
			HSSFRow row1 = sheet.createRow(numrow);

			sheet.addMergedRegion(new CellRangeAddress(numrow, numrow,
					(short) 0, (short) 5));
			row1.setHeightInPoints(20);
			row1.setRowStyle(style);
			tzdgd += row1.getHeight();
			HSSFCell cell1 = row1.createCell(0);
			cell1.setCellValue("收费通知单");
			cell1.setCellStyle(style);

			// 表格第三行
			numrow = sheetrow++;
			HSSFRow row3 = sheet.createRow(numrow);
			row3.setRowStyle(style3);
			tzdgd += row3.getHeight();
			// row3.setHeightInPoints(20);
			sheet.addMergedRegion(new CellRangeAddress(numrow, numrow,
					(short) 0, (short) 1));
			HSSFCell cell3 = row3.createCell(0);
			cell3.setCellValue("收费期间：" + sfqj);
			cell3.setCellStyle(stylecel);

			sheet.addMergedRegion(new CellRangeAddress(numrow, numrow,
					(short) 2, (short) 3));
			HSSFCell cell3_2 = row3.createCell(2);
			cell3_2.setCellValue("打印时间：" + printtime);

			cell3_2.setCellStyle(stylecel1);

			sheet.addMergedRegion(new CellRangeAddress(numrow, numrow,
					(short) 4, (short) 5));
			HSSFCell cell3_3 = row3.createCell(4);
			cell3_3.setCellValue("");
			cell3_3.setCellStyle(stylecel2);

			HSSFCell cell3_5 = row3.createCell(5);
			cell3_5.setCellStyle(stylecel2);

			// 表格第四行
			numrow = sheetrow++;
			HSSFRow row4 = sheet.createRow(numrow);
			row4.setRowStyle(style3);
			// row4.setHeightInPoints(15);
			tzdgd += row4.getHeight();
			sheet.addMergedRegion(new CellRangeAddress(numrow, numrow,
					(short) 0, (short) 1));
			HSSFCell cell4 = row4.createCell(0);
			cell4.setCellValue("小区名称：" + commName);
			cell4.setCellStyle(stylecel);

			sheet.addMergedRegion(new CellRangeAddress(numrow, numrow,
					(short) 2, (short) 3));
			HSSFCell cell4_2 = row4.createCell(2);
			cell4_2.setCellValue("房屋信息：" + fwxx);
			cell4_2.setCellStyle(stylecel1);

			sheet.addMergedRegion(new CellRangeAddress(numrow, numrow,
					(short) 4, (short) 5));
			// row4.setRowStyle(stylecel2);
			HSSFCell cell4_3 = row4.createCell(4);
			cell4_3.setCellValue("房屋建筑面积：" + fwjzmj + "㎡");
			cell4_3.setCellStyle(stylecel2);
			HSSFCell cell4_5 = row4.createCell(5);
			cell4_5.setCellStyle(stylecel2);

			// 表格第五行
			numrow = sheetrow++;
			HSSFRow row5 = sheet.createRow(numrow);
			tzdgd += row5.getHeight();
			// row5.setHeightInPoints(25);

			HSSFCell cell5_0 = row5.createCell(0);
			cell5_0.setCellValue("费用名称");
			cell5_0.setCellStyle(style5);

			HSSFCell cell5_1 = row5.createCell(1);
			cell5_1.setCellValue("计费用量");
			cell5_1.setCellStyle(style5);

			HSSFCell cell5_2 = row5.createCell(2);
			cell5_2.setCellValue("单价(元)");
			cell5_2.setCellStyle(style5);

			HSSFCell cell5_3 = row5.createCell(3);
			cell5_3.setCellValue("本期应缴(元)");
			cell5_3.setCellStyle(style5);

			HSSFCell cell5_4 = row5.createCell(4);
			cell5_4.setCellValue("历史欠费(元)");
			cell5_4.setCellStyle(style5);

			HSSFCell cell5_5 = row5.createCell(5);
			cell5_5.setCellValue("应缴合计(元)");
			cell5_5.setCellStyle(style5);
			// 获取该房屋的欠费信息
			Map<String, Object> fwqfmap = new HashMap<String, Object>();
			fwqfmap.put("apartment_id", map.get("apartment_id"));
			fwqfmap.put("startTime", startTime);
			fwqfmap.put("endTime", endTime);
			/*List<Map<String, Object>> fwqflist = this.getSqlSession()
					.selectList("PropNoticeMapper.getfhqfmxtzd", fwqfmap);
			System.out.println("获取该房屋的欠费信息==============:" + fwqflist);*/
			//
			// feeTypeName,
			// IFNULL(feeAmount, '-') feeAmount,
			// IFNULL(feePrice, '-') feePrice,
			// IFNULL(amount, '-') amount,
			// IFNULL(oldamount, '-') oldamount,
			// IFNULL(oldstardate, '-') oldstardate,
			// IFNULL(oldenddate, '-') oldenddate,
			// IFNULL(amount, 0) + IFNULL(oldamount, 0) sumamount
			/*---------------------------
			// 表格第六行
			numrow = sheetrow++;
			HSSFRow row6 = sheet.createRow(numrow);
			tzdgd += row6.getHeight();
			// row6.setHeightInPoints(25);
			HSSFCell cell6_0 = row6.createCell(0);
			cell6_0.setCellValue("物业管理费");
			cell6_0.setCellStyle(style6);
			
			HSSFCell cell6_1 = row6.createCell(1);
			cell6_1.setCellValue("130");
			cell6_1.setCellStyle(style6);

			HSSFCell cell6_2 = row6.createCell(2);
			cell6_2.setCellValue("3.00");
			cell6_2.setCellStyle(style6);

			HSSFCell cell6_3 = row6.createCell(3);
			cell6_3.setCellValue("");
			cell6_3.setCellStyle(style6);

			HSSFCell cell6_4 = row6.createCell(4);
			cell6_4.setCellValue("");
			cell6_4.setCellStyle(style6);

			HSSFCell cell6_5 = row6.createCell(5);
			cell6_5.setCellValue("");
			cell6_5.setCellStyle(style6);

			// 表格第七行
			numrow = sheetrow++;
			HSSFRow row7 = sheet.createRow(numrow);
			tzdgd += row7.getHeight();
			// row7.setHeightInPoints(25);
			HSSFCell cell7_0 = row7.createCell(0);
			cell7_0.setCellValue("公共维修金");
			cell7_0.setCellStyle(style6);

			HSSFCell cell7_1 = row7.createCell(1);
			cell7_1.setCellValue("130");
			cell7_1.setCellStyle(style6);

			HSSFCell cell7_2 = row7.createCell(2);
			cell7_2.setCellValue("3.00");
			cell7_2.setCellStyle(style6);

			HSSFCell cell7_3 = row7.createCell(3);
			cell7_3.setCellValue("");
			cell7_3.setCellStyle(style6);

			HSSFCell cell7_4 = row7.createCell(4);
			cell7_4.setCellValue("");
			cell7_4.setCellStyle(style6);

			HSSFCell cell7_5 = row7.createCell(5);
			cell7_5.setCellValue("");
			cell7_5.setCellStyle(style6);

			// 表格第八行
			numrow = sheetrow++;
			HSSFRow row8 = sheet.createRow(numrow);
			tzdgd += row8.getHeight();
			// row8.setHeightInPoints(25);
			HSSFCell cell8_0 = row8.createCell(0);
			cell8_0.setCellValue("照明用电公摊");
			cell8_0.setCellStyle(style6);

			HSSFCell cell8_1 = row8.createCell(1);
			cell8_1.setCellValue("-");
			cell8_1.setCellStyle(style6);

			HSSFCell cell8_2 = row8.createCell(2);
			cell8_2.setCellValue("0.533");
			cell8_2.setCellStyle(style6);

			HSSFCell cell8_3 = row8.createCell(3);
			cell8_3.setCellValue("20.00");
			cell8_3.setCellStyle(style6);

			HSSFCell cell8_4 = row8.createCell(4);
			cell8_4.setCellValue("29.00（2015.10-2015.12）");
			cell8_4.setCellStyle(style6);

			HSSFCell cell8_5 = row8.createCell(5);
			cell8_5.setCellValue("49.00");
			cell8_5.setCellStyle(style6);

			// 表格第9行
			numrow = sheetrow++;
			HSSFRow row9 = sheet.createRow(numrow);
			tzdgd += row9.getHeight();
			// row9.setHeightInPoints(25);
			HSSFCell cell9_0 = row9.createCell(0);
			cell9_0.setCellValue("消防用水公摊");
			cell9_0.setCellStyle(style6);

			HSSFCell cell9_1 = row9.createCell(1);
			cell9_1.setCellValue("-");
			cell9_1.setCellStyle(style6);

			HSSFCell cell9_2 = row9.createCell(2);
			cell9_2.setCellValue("0.533");
			cell9_2.setCellStyle(style6);

			HSSFCell cell9_3 = row9.createCell(3);
			cell9_3.setCellValue("20.00");
			cell9_3.setCellStyle(style6);

			HSSFCell cell9_4 = row9.createCell(4);
			cell9_4.setCellValue("29.00（2015.10-2015.12）");
			cell9_4.setCellStyle(style6);

			HSSFCell cell9_5 = row9.createCell(5);
			cell9_5.setCellValue("49.00");
			cell9_5.setCellStyle(style6);

			// 表格第10行
			numrow = sheetrow++;
			HSSFRow row10 = sheet.createRow(numrow);
			tzdgd += row10.getHeight();
			// row10.setHeightInPoints(25);
			HSSFCell cell10_0 = row10.createCell(0);
			cell10_0.setCellValue("电梯用电公摊");
			cell10_0.setCellStyle(style6);

			HSSFCell cell10_1 = row10.createCell(1);
			cell10_1.setCellValue("-");
			cell10_1.setCellStyle(style6);

			HSSFCell cell10_2 = row10.createCell(2);
			cell10_2.setCellValue("0.533");
			cell10_2.setCellStyle(style6);

			HSSFCell cell10_3 = row10.createCell(3);
			cell10_3.setCellValue("20.00");
			cell10_3.setCellStyle(style6);

			HSSFCell cell10_4 = row10.createCell(4);
			cell10_4.setCellValue("29.00（2015.10-2015.12）");
			cell10_4.setCellStyle(style6);

			HSSFCell cell10_5 = row10.createCell(5);
			cell10_5.setCellValue("49.00");
			cell10_5.setCellStyle(style6);

			------------------------------*/
			// 第11行
			numrow = sheetrow++;
			HSSFRow row11 = sheet.createRow(numrow);
			tzdgd += row11.getHeight();
			// row11.setHeightInPoints(25);
			row11.setRowStyle(style11);
			sheet.addMergedRegion(new CellRangeAddress(numrow, numrow,
					(short) 0, (short) 2));
			HSSFCell cell11_0 = row11.createCell(0);
			cell11_0.setCellValue("缴费期限：" + limitDate);
			cell11_0.setCellStyle(style11);

			sheet.addMergedRegion(new CellRangeAddress(numrow, numrow,
					(short) 3, (short) 5));
			HSSFCell cell11_1 = row11.createCell(3);
			cell11_1.setCellValue("合计应缴金额（小写）：                                150.00");
			cell11_1.setCellStyle(style11);
			// 第12行
			numrow = sheetrow++;
			HSSFRow row12 = sheet.createRow(numrow);
			row12.setHeightInPoints(40);
			tzdgd += row12.getHeight();
			row12.setRowStyle(style12);
			HSSFCell cell12_0 = row12.createCell(0);
			cell12_0.setCellValue("备注：");
			cell12_0.setCellStyle(style12);

			sheet.addMergedRegion(new CellRangeAddress(numrow, numrow,
					(short) 1, (short) 5));
			HSSFCell cell12_1 = row12.createCell(1);
			cell12_1.setCellValue(remark);
			cell12_1.setCellStyle(style12);

			numrow = sheetrow++;
			HSSFRow row13 = sheet.createRow(numrow);
			// row13.setHeightInPoints(200);
			tzdgd += row13.getHeight();
			// System.out.println("通知单打印高度:"+tzdgd);
			int pagesize = 18000 / tzdgd;
			if (i % pagesize == 0 && i != 0) {

				sheet.setAutobreaks(false);
				sheet.setRowBreak(numrow);// 設置每30行分頁打印
			}

		}

		// HSSFWorkbook wb = studentExportService.export(list);
		// response.setContentType("application/vnd.ms-excel");
		// response.setHeader("Content-disposition",
		// "attachment;filename=student.xls");
		// OutputStream ouputStream = response.getOutputStream();
		// wb.write(ouputStream);
		// ouputStream.flush();
		// ouputStream.close();

		ByteArrayOutputStream os = new ByteArrayOutputStream();
		try {
			wb.write(os);
			byte[] bytes = os.toByteArray();

		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		FileOutputStream fileOut = null;
		try {
			fileOut = new FileOutputStream("d:\\workbook.xls");
			wb.write(fileOut);
			// fileOut.close();
			System.out.print("OK");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (fileOut != null) {
				try {
					fileOut.close();
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
	}
	
	
	@Test
	public void outPaymentSchedule(){
		
		HSSFWorkbook wb = new HSSFWorkbook(); // --->创建了一个excel文件
		HSSFSheet sheet = wb.createSheet("收费通知单"); // --->创建了一个工作簿

		HSSFPrintSetup printSetup = sheet.getPrintSetup();
		printSetup.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE); // 纸张
		// sheet.setDisplayGridlines(false);
		// sheet.setPrintGridlines(false);System.out.println("vvvvvvvvvvvvvv:"+printSetup.getFitHeight());
		sheet.setMargin(HSSFSheet.TopMargin, (double) 0.5); // 上边距
		sheet.setMargin(HSSFSheet.BottomMargin, (double) 0.5); // 下边距
		sheet.setMargin(HSSFSheet.LeftMargin, (double) 0.5); // 左边距
		sheet.setMargin(HSSFSheet.RightMargin, (double) 0.5); // 右边距
		
		HSSFDataFormat format = wb.createDataFormat(); // --->单元格内容格式
		sheet.setColumnWidth((short) 0, 20 * 190); // ---》设置单元格宽度，因为一个单元格宽度定了那么下面多有的单元格高度都确定了所以这个方法是sheet的
		sheet.setColumnWidth((short) 1, 20 * 170); // --->第一个参数是指哪个单元格，第二个参数是单元格的宽度
		sheet.setColumnWidth((short) 2, 20 * 170);
		sheet.setColumnWidth((short) 3, 20 * 170);
		sheet.setColumnWidth((short) 4, 20 * 330);
		sheet.setColumnWidth((short) 5, 20 * 170);
		sheet.setDefaultRowHeight((short) 300); // ---->有得时候你想设置统一单元格的高度，就用这个方法
		sheet.protectSheet("123456");
		
		int sheetrow = 0;
		int numrow = 0;
		/** 所有行的样式开始 **/
		// 样式1 第一行和第二行样式
		HSSFCellStyle style = wb.createCellStyle(); // 样式对象
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
		style.setBorderBottom((short) 1); // 设置下划线，参数是黑线的宽度
		style.setBorderLeft((short) 1); // 设置左边框
		style.setBorderRight((short) 1); // 设置有边框
		style.setBorderTop((short) 1); // 设置下边框
		Font font = wb.createFont();
		// font.setFontName("Courier New"); //---》设置字体，是什么类型例如：宋体
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗
		// font.setItalic(true); //--->设置是否是加粗
		font.setFontHeightInPoints((short) 10); // --->设置字体大小
		style.setFont(font);

		// 第三行样式
		HSSFCellStyle style3 = wb.createCellStyle(); // 样式对象
		style3.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直
		style3.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
		// style3.setBorderBottom((short)0); //设置下划线，参数是黑线的宽度
		// style3.setBorderLeft((short)0); //设置左边框
		// style3.setBorderRight((short)1); //设置有边框
		// style3.setBorderTop((short)0); //设置下边框
		Font font3 = wb.createFont();
		font3.setFontHeightInPoints((short) 5); // --->设置字体大小
		style3.setFont(font3);

		HSSFCellStyle stylecel = wb.createCellStyle(); // 样式对象
		stylecel.setBorderBottom((short) 0); // 设置下划线，参数是黑线的宽度
		stylecel.setBorderLeft((short) 1); // 设置左边框
		stylecel.setBorderRight((short) 0); // 设置有边框
		stylecel.setBorderTop((short) 0); // 设置下边框

		HSSFCellStyle stylecel1 = wb.createCellStyle(); // 样式对象
		stylecel1.setBorderBottom((short) 0); // 设置下划线，参数是黑线的宽度
		stylecel1.setBorderLeft((short) 0); // 设置左边框
		stylecel1.setBorderRight((short) 0); // 设置有边框
		stylecel1.setBorderTop((short) 0); // 设置下边框

		HSSFCellStyle stylecel2 = wb.createCellStyle(); // 样式对象
		stylecel2.setBorderBottom((short) 0); // 设置下划线，参数是黑线的宽度
		stylecel2.setBorderLeft((short) 0); // 设置左边框
		stylecel2.setBorderRight((short) 1); // 设置有边框
		stylecel2.setBorderTop((short) 0); // 设置下边框

		// 样式1 第一行和第二行样式
		HSSFCellStyle style5 = wb.createCellStyle(); // 样式对象
		style5.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直
		style5.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
		style5.setBorderBottom((short) 1); // 设置下划线，参数是黑线的宽度
		style5.setBorderLeft((short) 1); // 设置左边框
		style5.setBorderRight((short) 1); // 设置有边框
		style5.setBorderTop((short) 1); // 设置下边框
		Font font5 = wb.createFont();
		font5.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗
		// font.setFontName("Courier New"); //---》设置字体，是什么类型例如：宋体
		// font5.setItalic(true); //--->设置斜体
		font5.setFontHeightInPoints((short) 8); // --->设置字体大小
		// font5.set
		style5.setFont(font5);

		HSSFCellStyle style6 = wb.createCellStyle(); // 样式对象
		style6.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直
		style6.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
		style6.setBorderBottom((short) 1); // 设置下划线，参数是黑线的宽度
		style6.setBorderLeft((short) 1); // 设置左边框
		style6.setBorderRight((short) 1); // 设置有边框
		style6.setBorderTop((short) 1); // 设置下边框
		style6.setWrapText(true); // 设置是否能够换行，能够换行为true

		// 表格第11行
		HSSFCellStyle style11 = wb.createCellStyle(); // 样式对象
		style11.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直
		// style11.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
		style11.setBorderBottom((short) 1); // 设置下划线，参数是黑线的宽度
		style11.setBorderLeft((short) 1); // 设置左边框
		style11.setBorderRight((short) 1); // 设置有边框
		style11.setBorderTop((short) 1); // 设置下边框

		// 表格第12行
		HSSFCellStyle style12 = wb.createCellStyle(); // 样式对象
		style12.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直
		style12.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
		style12.setBorderBottom((short) 1); // 设置下划线，参数是黑线的宽度
		style12.setBorderLeft((short) 1); // 设置左边框
		style12.setBorderRight((short) 1); // 设置有边框
		style12.setBorderTop((short) 1); // 设置下边框
		style12.setWrapText(true); // 设置是否能够换行，能够换行为true
		/** 所有行的样式结束 **/
		
		ByteArrayOutputStream os = new ByteArrayOutputStream();
		try {
			wb.write(os);
			byte[] bytes = os.toByteArray();

		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		FileOutputStream fileOut = null;
		try {
			fileOut = new FileOutputStream("d:\\workbook.xls");
			wb.write(fileOut);
			// fileOut.close();
			System.out.print("OK");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (fileOut != null) {
				try {
					fileOut.close();
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		
	}

	public static void main(String[] args) throws AppBizException,
			ParseException {

		String f = "%" + 10 + "s";
		System.out.println(String.format(f, "ddpppp"));
		System.out.println(String.format(f, "555.0"));
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		Map<String, Object> map1 = new HashMap<String, Object>();
		
		map1.put("building_name", "1");
		map1.put("unit_name", "2");
		map1.put("apartment_num","3");
		map1.put("fee_area","4");
		
		Map<String, Object> map2 = new HashMap<String, Object>();
		
		map2.put("building_name", "5");
		map2.put("unit_name", "6");
		map2.put("apartment_num","7");
		map2.put("fee_area","8");
		
		list.add(map1);
		list.add(map2);
		
		ExportFeeNotice ex = new ExportFeeNotice();
		ex.outExcel(list, f, f, f, f, f, f);
		
		

	}

}
